package dao;

import entity.Group;
import entity.User;
import groupInformation.Relation;

import java.sql.*;

public class AddDAO {
    /**
     * 添加小组功能
     *
     * @author 老顾 And 藤树
     */

    public static boolean insertGroup(Group group) throws Exception {
        int insetGroup_state = 1;
        Connection conn = null;
        PreparedStatement ps = null;
        int inserRelationStatus = 0;
        ResultSet rs = null;
//        Group group =new Group("藤树",1236778,"超级赛亚人组合","你相信光吗？变身吧，黑丝赛亚人！","拯救一个小型星系","保卫爱与正义，维护宇宙和平！","黑丝 赛亚人 正义使者",10010);
        String url = "jdbc:mysql://82.156.38.140:3306/team_management_system? &useSSL=false&serverTimezone=UTC";
        String user = "root";
        String password = "123456";
        try {
            //1、获取驱动
            DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
            //2、获取连接
            conn = DriverManager.getConnection(url, user, password);
            String sql = "insert into Group_Information " +
                    "(owner,id,owner_id,name,profile,task,announcement,tag)" +
                    "values(?,?,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            //给?传值
            ps.setString(1, group.getOwner());
            ps.setInt(2, group.getId());
            ps.setInt(3, group.getOwner_id());
            ps.setString(4, group.getName());
            ps.setString(5, group.getProfile());
            ps.setString(6, group.getTask());
            ps.setString(7, group.getAnnouncement());
            ps.setString(8, group.getTag());
            ps.executeUpdate();
            inserRelationStatus = AddDAO.insertNewRelation(group.getOwner_id(), group.getId());

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }

            if (conn != null) {
                conn.close();
            }
        }
        return (inserRelationStatus + insetGroup_state) == 2 ? true : false;
    }


    /**
     * 增加组员
     *
     * @author 老顾 And 藤树 And 顾青山
     */
    public static Relation insertGroupUser(Group group,User user) {
//        Group group =new Group("藤树",1236778,"超级赛亚人组合","你相信光吗？变身吧，黑丝赛亚人！","拯救一个小型星系","保卫爱与正义，维护宇宙和平！","黑丝 赛亚人 正义使者",10010);
//        User user = new User(123456,"Gn002","123456","顾青山02","ins ert测试成功！","男","1",33,"蛤蟆仙人","木叶村 上忍 lsp");
        int insetGroup_state = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String url = "jdbc:mysql://82.156.38.140:3306/team_management_system? &useSSL=false&serverTimezone=UTC";
        String sqlUser = "root";
        String sqlPassword = "123456";
        String sqlSelect = "select * from Group_Relation where group_id=?";
        String sqlUpdate = "update Group_Relation set member_id? = ? where group_id = ?";
        int n = 0;//查找为null的member_id所在列
        Relation relation = null;

        try {
            //1、获取驱动
            DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
            //2、获取连接
            conn = DriverManager.getConnection(url, sqlUser, sqlPassword);
            PreparedStatement st = conn.prepareStatement(sqlSelect);
            st.setObject(1, group.getId());
            rs = st.executeQuery();
            rs.next();//让光标移动
            String order = null;

            //找到空的member_id列数
            for (n = 1; n <= 9; n++) {
                if (rs.getString("member_id" + n) == null || rs.getInt("member_id"+ n)== 0) {
                    order = rs.getString("member_id" + n);
                    break;//break后，n即我们要找的列
                } else {
                    continue;
                }
            }
            System.out.println(n);
            ps = conn.prepareStatement(sqlUpdate);
            ps.setObject(1, n);
            ps.setObject(2, user.getId());
            ps.setObject(3, group.getId());

            System.out.println(sqlUpdate);
            //执行sql
            insetGroup_state = ps.executeUpdate();
            System.out.println("AddDao insertGroupUser --> 向" + group.getName() + " 小组插入了" + insetGroup_state + "位成员！");
            if (insetGroup_state == 1) {
                st = conn.prepareStatement(sqlSelect);
                st.setInt(1, group.getId());
                ResultSet rs2 = st.executeQuery();
                while (rs2.next()) {
                    relation = new Relation(rs2.getInt("owner"), rs2.getInt("group_id"), 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
                    if (rs2.getString("member_id1") != null || rs2.getInt("member_id1") != 0 ) {
                        relation.setMember_id1(rs2.getInt("member_id1"));
                    }
                    if (rs2.getString("member_id2") != null || rs2.getInt("member_id2") != 0) {
                        relation.setMember_id2(rs2.getInt("member_id2"));
                    }
                    if (rs2.getString("member_id3") != null || rs2.getInt("member_id3") != 0) {
                        relation.setMember_id3(rs2.getInt("member_id3"));
                    }
                    if (rs2.getString("member_id4") != null || rs2.getInt("member_id4") != 0) {
                        relation.setMember_id4(rs2.getInt("member_id4"));
                    }
                    if (rs.getString("member_id5") != null || rs.getInt("member_id5") != 0) {
                        relation.setMember_id5(rs.getInt("member_id5"));
                    }
                    if (rs2.getString("member_id6") != null || rs2.getInt("member_id6") != 0) {
                        relation.setMember_id6(rs2.getInt("member_id6"));
                    }
                    if (rs2.getString("member_id7") != null || rs2.getInt("member_id7") != 0) {
                        relation.setMember_id7(rs2.getInt("member_id7"));
                    }
                    if (rs2.getString("member_id8") != null || rs2.getInt("member_id8") != 0) {
                        relation.setMember_id8(rs2.getInt("member_id8"));
                    }
                    if (rs2.getString("member_id9") != null || rs2.getInt("member_id9") != 0) {
                        relation.setMember_id9(rs2.getInt("member_id9"));
                    }
                    if (rs2.getString("member_id10") != null || rs2.getInt("member_id10") != 0) {
                        relation.setMember_id10(rs2.getInt("member_id10"));
                    }

                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
               try {
                   rs.close();
               }catch (SQLException e){
                   e.printStackTrace();
               }
            }
            if (ps != null) {
                try {
                    ps.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
        return relation;
    }

    // 判断小组是否存在  by 顾青山
    public static boolean search(Group group){
//        Group group =new Group("藤树",1236778,"超级赛亚人组合","你相信光吗？变身吧，黑丝赛亚人！","拯救一个小型星系","保卫爱与正义，维护宇宙和平！","黑丝 赛亚人 正义使者",10010);

        String url = "jdbc:mysql://82.156.38.140:3306/team_management_system?autoReconnect=TRUE&serverTimezone=UTC";
        String sqlUser = "root";
        String sqlPassword = "123456";
        Connection connection = null;
        PreparedStatement pstm = null;
        String sql = "select * from Group_Information where name ='" + group.getName() + "'";
        boolean group_exist = false;
        try {
            DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
            connection = DriverManager.getConnection(url, sqlUser, sqlPassword);
            pstm = connection.prepareStatement(sql);
            ResultSet rs = pstm.executeQuery();
            if (rs.next() == false) {
                System.out.println("AddDao search() --> "+group.getName() + "小组不存在!");
            }else {
                group_exist = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstm != null) {// pstm不为空时才关闭
                    pstm.close();
                }
                if (connection != null) {// 同上
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return group_exist;
    }

    // 此方法被创建小组方法调用： 创建小组的时候，往Relation表插入数据。 by 顾青山
    public static int insertNewRelation(int owner,int group_id){
        int status = 0;
        Connection connection = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        String url = "jdbc:mysql://82.156.38.140:3306/team_management_system? &useSSL=false&serverTimezone=UTC";
        String sqlUser = "root";
        String sqlPassword = "123456";
        String sqlOfInsertNewRelation = "insert into Group_Relation values(?,?,0,0,0,0,0,0,0,0,0,0)";
        try {
            //1、获取驱动
            DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
            //2、获取连接
            connection = DriverManager.getConnection(url,sqlUser,sqlPassword);
            pstm = connection.prepareStatement(sqlOfInsertNewRelation);
            pstm.setInt(1,owner);
            pstm.setInt(2,group_id);
            status = pstm.executeUpdate();
        }catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstm != null) {// pstm不为空时才关闭
                    pstm.close();
                }
                if (connection != null) {// 同上
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return status;
    }
}
